﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Text;


public partial class SpecialPlan_ImportSpecialPlan : System.Web.UI.Page
{
    string path = HttpContext.Current.Server.MapPath("~/") + @"Upload\Excel\";
    string Sponsor = Utility.HttpHelp.getParamsString("Sponsor");
    int VerifyCircuitId = Utility.HttpHelp.getParamsInt("VerifyCircuitId");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.drpDepartment.DataSource = new BLL.DepartmentInfoManager().getNoTree();
            this.drpDepartment.DataTextField = "DepartmentName";
            this.drpDepartment.DataValueField = "DepartmentId";
            this.drpDepartment.DataBind();

        }
    }



    /// <summary>
    /// 读取Excel内容
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnImport_Click(object sender, EventArgs e)
    {

        if (fupExcelPath.HasFile)
        {
            string _filename = this.fupExcelPath.FileName;
            //判断目录是否存在
            if (System.IO.Directory.Exists(path))
            {
                //保存
                fupExcelPath.SaveAs(path + _filename);
            }
            else
            {
                System.IO.Directory.CreateDirectory(path);
                //保存
                fupExcelPath.SaveAs(path + _filename);
            }
            string ExcelPath = path + _filename;
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelPath + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = new OleDbCommand();
                adapter.SelectCommand.Connection = conn;
                DataSet excelData = new DataSet();

                if (schemaTable.Rows.Count > 0)
                {
                    adapter.SelectCommand.CommandText = string.Format("SELECT * FROM [{0}]", schemaTable.Rows[0]["TABLE_NAME"]);
                    adapter.Fill(excelData);
                }
                conn.Close();
                ImportSpecialPlan(excelData.Tables[0]);
            }
            catch (Exception ex)
            {
                conn.Close();
            }
        }
    }


    /// <summary>
    /// 导入年计划
    /// </summary>
    void ImportSpecialPlan(DataTable dt)
    {
        object[] _valueDepartmentName = new object[1];
        _valueDepartmentName[0] = this.drpDepartment.SelectedItem.Text;
        DataTable _VerifyCircuitId = new BLL.VerifyCircuitManager().getVerifyCircuitIdByDepartment(_valueDepartmentName);
        if (_VerifyCircuitId.Rows.Count > 0)
        {
            VerifyCircuitId = Convert.ToInt32(_VerifyCircuitId.Rows[0]["VerifyCircuitId"].ToString());
        }
        Model.UserInfo objUserInfo = new Model.UserInfo();
        SqlParameter[] SpecialResolveParameters = null;
        SqlParameter[] SpecialParameters = null;
        int SpecialPlanId = 0;

        using (SqlConnection conn = new SqlConnection(DAL.SqlHelper.connStr))
        {
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            try
            {

                //遍历Table
                foreach (DataRow dr in dt.Rows)
                {
                    //判断是否为分解计划
                    if (dr["计划编号"].ToString().Contains("."))
                    {
                        SpecialResolveParameters = new SqlParameter[22];
                        SpecialResolveParameters[0] = new SqlParameter("@SpecialPlanDetailSortId", dr["计划编号"].ToString());
                        SpecialResolveParameters[1] = new SqlParameter("@SpecialPlanId", SpecialPlanId);
                        SpecialResolveParameters[2] = new SqlParameter("@StartTime", Convert.ToDateTime(dr["起始时间"]));
                        SpecialResolveParameters[3] = new SqlParameter("@CompleteTime", Convert.ToDateTime(dr["完成时间"]));


                        SpecialResolveParameters[4] = new SqlParameter("@WorkItem", dr["计划名称"].ToString());
                        SpecialResolveParameters[5] = new SqlParameter("@PreposeTask", string.Empty);
                        SpecialResolveParameters[6] = new SqlParameter("@WorkItemDesc", string.Empty);

                        SpecialResolveParameters[7] = new SqlParameter("@MasterPeople", dr["责任人"].ToString());
                        SpecialResolveParameters[8] = new SqlParameter("@MasterDepartment", dr["责任部门"].ToString());
                        SpecialResolveParameters[9] = new SqlParameter("@SlaveDepartment", FilterToString(dr["配合部门"].ToString().Replace("，", ",")));
                        SpecialResolveParameters[10] = new SqlParameter("@SlaveDepartmentDuty", dr["配合部门责任"] == null ? "" : dr["配合部门责任"].ToString());
                        SpecialResolveParameters[11] = new SqlParameter("@EvaluatePeople", dr["评估人"] == null ? "" : dr["评估人"].ToString());

                        SpecialResolveParameters[12] = new SqlParameter("@CheckNote", string.Empty);
                        SpecialResolveParameters[13] = new SqlParameter("@State", Convert.ToInt32("3"));
                        SpecialResolveParameters[14] = new SqlParameter("@PriorityLevel", dr["优先级"].ToString());
                        SpecialResolveParameters[15] = new SqlParameter("@ImportantDegree", string.Empty);
                        SpecialResolveParameters[16] = new SqlParameter("@CompleteState", string.Empty);
                        SpecialResolveParameters[17] = new SqlParameter("@CompleteDesc", string.Empty);
                        SpecialResolveParameters[18] = new SqlParameter("@VerifyPeople", string.Empty);
                        SpecialResolveParameters[19] = new SqlParameter("@VerifyCircuitId", VerifyCircuitId);
                        SpecialResolveParameters[20] = new SqlParameter("@Sponsor", GetSponsor(dr["责任人"].ToString()));//"admin");
                        SpecialResolveParameters[21] = new SqlParameter("@Remart", dr["备注"] == null ? "" : dr["备注"].ToString());


                        DAL.SqlHelper.ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "Insert_SpecialPlanDetail", SpecialResolveParameters);
                    }
                    else
                    {
                        SpecialParameters = new SqlParameter[15];
                        SpecialParameters[0] = new SqlParameter("@SpecialPlanSortId", Convert.IsDBNull(dr["计划编号"]) == true ? 1 : Convert.ToInt32(dr["序号"]));
                        SpecialParameters[1] = new SqlParameter("@WorkItem", dr["计划名称"].ToString());
                        SpecialParameters[2] = new SqlParameter("@StartTime", Convert.ToDateTime(dr["起始时间"]));
                        SpecialParameters[3] = new SqlParameter("@CompleteTime", Convert.ToDateTime(dr["完成时间"]));

                        SpecialParameters[4] = new SqlParameter("@MasterDepartment", dr["责任部门"].ToString());
                        SpecialParameters[5] = new SqlParameter("@MasterPeople", dr["责任人"].ToString());
                        SpecialParameters[6] = new SqlParameter("@SlaveDepartment", FilterToString(dr["配合部门"].ToString().Replace("，", ",")));
                        SpecialParameters[7] = new SqlParameter("@SlaveDepartmentDuty", dr["配合部门责任"] == null ? "" : dr["配合部门责任"].ToString());
                        SpecialParameters[8] = new SqlParameter("@EvaluatePeople", dr["评估人"]);
                        SpecialParameters[9] = new SqlParameter("@State", Convert.ToInt32("3"));
                        SpecialParameters[10] = new SqlParameter("@PriorityLevel", dr["优先级"].ToString());
                        SpecialParameters[11] = new SqlParameter("@ImportantDegree", string.Empty);
                        SpecialParameters[12] = new SqlParameter("@VerifyCircuitId", VerifyCircuitId);
                        SpecialParameters[13] = new SqlParameter("@Sponsor", GetSponsor(dr["责任人"].ToString()));//"admin");
                        SpecialParameters[14] = new SqlParameter("@Remart", dr["备注"] == null ? "" : dr["备注"].ToString());


                        DAL.SqlHelper.ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "Insert_SpecialPlanWithExcel", SpecialParameters);
                        SpecialPlanId = Convert.ToInt32(DAL.SqlHelper.ExecuteScalar(conn, trans, CommandType.Text, "select top 1 Id from DS_SpecialPlan order by Id desc"));

                    }
                }

                trans.Commit();
                Utility.HttpHelp.alert("导入成功", this.Page);
            }
            catch (System.Exception ex)
            {
                trans.Rollback();
                Utility.HttpHelp.alert(ex.Message, this.Page);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }

    /// <summary>
    /// 替换空格顿号为,号
    /// </summary>
    /// <param name="strSource"></param>
    /// <returns></returns>
    string FilterPlace(string strSource)
    {
        if (strSource.Contains(" ") || strSource.Contains("、"))
        {
            strSource = strSource.Replace(" ", "");
            strSource = strSource.Replace("、", "");
            FilterPlace(strSource);
        }
        return strSource;
    }


    /// <summary>
    /// 过滤重复,号返回字符串
    /// </summary>
    /// <param name="strSource"></param>
    /// <returns></returns>
    string FilterToString(string strSource)
    {
        strSource = FilterPlace(strSource);
        string SlaveDepartmentSource = GetSlaveDepartment();
        string[] result = SlaveDepartmentSource.Split(',');
        StringBuilder strResult = new StringBuilder();

        foreach (string var in result)
        {
            if (strSource.Contains(var))
            {
                if (var.Trim().Length != 0)
                {
                    strResult.Append(var + ",");
                }
            }
        }
        if (strResult.ToString().Length > 0)
        {
            strResult = strResult.Remove(strResult.Length - 1, 1);
        }
        return strResult.ToString();
    }

    /// <summary>
    /// 获取所有的配合部门名单
    /// </summary>
    /// <returns></returns>
    string GetSlaveDepartment()
    {
        StringBuilder str = new StringBuilder();
        string sql = "select RealName from DS_UserInfo";
        DataTable dt = DAL.SqlHelper.ExecuteDataset(DAL.SqlHelper.connStr, CommandType.Text, sql).Tables[0];
        foreach (DataRow dr in dt.Rows)
        {
            str.Append(dr["RealName"] + ",");
        }
        str.Append("全部部门" + "," + "独立完成" + "," + "关联部门" + "," + "卢志建," + "全体部门,");
        sql = "select DepartmentName from DS_DepartmentInfo";
        dt = DAL.SqlHelper.ExecuteDataset(DAL.SqlHelper.connStr, CommandType.Text, sql).Tables[0];
        foreach (DataRow dr in dt.Rows)
        {
            str.Append(dr["DepartmentName"] + ",");
        }
        return str.ToString();
    }

    /// <summary>
    /// 根据责任人获取发起人用户名
    /// </summary>
    /// <param name="masterPeople"></param>
    /// <returns></returns>
    string GetSponsor(string masterPeople)
    {
        string sql = "select username from ds_userinfo where isdelete=0 and isdimission=0 and  realname='" + masterPeople + "'";
        DataTable dt = DAL.SqlHelper.ExecuteDataset(DAL.SqlHelper.connStr, CommandType.Text, sql).Tables[0];
        foreach (DataRow dr in dt.Rows)
        {
            if (dr["username"].ToString().Length > 0)
            {
                return dr["username"].ToString();
            }
            else
            {
                return "";
            }
        }
        return "";
    }
}